package com.xyzh05.dao.hibernate;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.xyzh05.dao.NotepadDao;
import com.xyzh05.model.Notepad;

@Repository("notepadDao")
public class NotepadDaoHibernate extends GenericDaoHibernate<Notepad, Long> implements NotepadDao {

    public NotepadDaoHibernate() {
        super(Notepad.class);
    }
    
    /**
     * 查询签到、记事数据
     * @param userIds
     * @param notepadType
     * @param start
     * @param end
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<Notepad> searchNotepad(List<Long> userIds, Short notepadType, Integer start, Integer end){
    	Boolean group = false;
    	String sql = "select * from notepad where 1=1";
    	if(userIds != null && !"".equals(userIds)){
    		if(userIds.size() == 1){
    			sql += " and user_id = " + userIds.get(0);    			
    		}else{
    			sql += " and user_id in(" + userIds.toString().replaceAll("[\\[\\]]", "") + ")";
    			group = true;
    		}
    	}
    	if(notepadType != null && !"".equals(notepadType)){
    		sql += " and note_type_id = " + notepadType;
    	}
    	if(group){
    		sql += " and type != 100";
    	}
    	sql += " order by sign_in_time desc";
    	if(start != null && !"".equals(start) && end != null && !"".equals(end)){
    		sql += " limit " + start + "," + end;
    	}
    	return getSession().createSQLQuery(sql).addEntity(Notepad.class).list();
    }
 
    /**
     * 获取统计数据
     * @param userIds
     * @param notepadType
     * @return
     */
    @SuppressWarnings("unchecked")
	public List<Integer> getStatisticCount(List<Long> userIds, Short notepadType){
    	List<Integer> statisticList = new ArrayList<Integer>();
    	String sql = "";
    	Boolean group = false;
    	if(userIds != null && !"".equals(userIds)){
    		if(userIds.size() == 1){
    			sql += " and user_id = " + userIds.get(0);
    		}else{
    			sql += " and user_id in(" + userIds.toString().replaceAll("[\\[\\]]", "") + ")";  
    			group = true;
    		}
    	}
    	if(notepadType != null && !"".equals(notepadType)){
    		sql += " and note_type_id = " + notepadType;
    	}
    	if(group){
    		sql += " and type != 100";
    	}
    	if(!"".equals(sql)){
    		sql = "select * from (select count(*) as tc from notepad where 1=1 " + sql + ") as total_count,(select count(*) as dc from notepad where to_days(sign_in_time) = to_days(now()) " + sql + ") as day_count,(select count(*) as wc from notepad where yearweek(date_format(sign_in_time, '%Y%m%d'),1) = yearweek(now()) " + sql + ") as week_count,(select count(*) as mc from notepad where date_format(sign_in_time, '%Y%m') = date_format(curdate(), '%Y%m') " + sql + ") as month_count";    		
    	}else{
    		sql = "select * from (select count(*) as tc from notepad) as total_count,(select count(*) as dc from notepad where to_days(sign_in_time) = to_days(now())) as day_count,(select count(*) as wc from notepad where yearweek(date_format(sign_in_time, '%Y%m%d'),1) = yearweek(now())) as week_count,(select count(*) as mc from notepad where date_format(sign_in_time, '%Y%m') = date_format(curdate(), '%Y%m')) as month_count";
    	}
    	Query query = getSession().createSQLQuery(sql);
    	List<Object[]> list = query.list();
    	for(Object[] obj : list){
    		for(int i=0; i<obj.length; i++){
    			statisticList.add(((BigInteger) obj[i]).intValue());
    		}
    	}
    	return statisticList;
    }
}
